package com.danco.training.zaneuskaya.connection.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import com.danco.training.zaneuskaya.connection.dao.AbstractDao;
import com.danco.training.zaneuskaya.connection.dao.ClaimDao;
import com.danco.training.zaneuskaya.domain.Book;
import com.danco.training.zaneuskaya.domain.Claim;


public class ClaimDaoImpl extends AbstractDao<Claim,Integer> implements ClaimDao{
	
	

	
	public static String insertClaimQuery = "INSERT INTO claims (BOOK_ID, AMOUNT, EXECUTED) VALUES (?,?,?)";
	public static String updateClaimQuery = "UPDATE claims SET BOOK_ID = ?, AMOUNT = ?, EXECUTED = ?  WHERE C_ID = ?" ;
	public static String selectSortedClaimsByAmountQuery = "SELECT * FROM claims,books WHERE claims.BOOK_ID = books.B_ID ORDER BY AMOUNT ";
	public static String selectClaimsSortedByAlphabetQuery = "SELECT * FROM claims, books WHERE claims.BOOK_ID = books.B_ID ORDER BY books.TITLE; ";
	public static String lastInsertedClaimQuery = "SELECT * FROM claims,books WHERE C_ID = LAST_INSERT_ID() AND claims.BOOK_ID=books.B_ID";
	public static String readClaimQuery = "SELECT * FROM claims,books WHERE C_ID  = ? AND claims.BOOK_ID=books.B_ID";
	public static String readAllClaimsQuery = "SELECT * FROM claims, books WHERE books.B_ID = claims.BOOK_ID";
	public static String deleteClaimQuery = "DELETE FROM claims WHERE C_ID = ?";
	public static String executeClaimQuery = "UPDATE claims SET  EXECUTED = 1  WHERE C_ID = ?" ;
	public static String deleteClaimByBook = "DELETE FROM claims WHERE BOOK_ID = ?";

	public static String findClaimByBook = "SELECT * FROM claims,books WHERE BOOK_ID = ? AND claims.BOOK_ID = books.B_ID";
	
	private static final Logger logger = Logger
			.getLogger(ClaimDaoImpl.class);

	@Override
	public List<Claim> readClaimsByAlphabet(Connection connection) {
		List<Claim> claims = new ArrayList<Claim>();
		try( 
			 PreparedStatement preparedStatement = connection.prepareStatement(selectClaimsSortedByAlphabetQuery);	
				){
			
			ResultSet result = preparedStatement.executeQuery();
			while (result.next()){
				claims.add(convertResultSet(result));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return claims;
	}

	@Override
	public List<Claim> readClaimsByClaimAmount(Connection connection) {
		List<Claim> claims = new ArrayList<Claim>();
		try( 
			 PreparedStatement preparedStatement = connection.prepareStatement(selectSortedClaimsByAmountQuery);	
				){
			
			ResultSet result = preparedStatement.executeQuery();
			while (result.next()){
				claims.add(convertResultSet(result));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return claims;
	}

	@Override
	public Claim convertResultSet(ResultSet resultSet) throws SQLException {
				
		int id = resultSet.getInt("C_ID");
	
		int amount = resultSet.getInt("AMOUNT");
		int executed = resultSet.getInt("EXECUTED");

		int bookId = resultSet.getInt("BOOK_ID");
		String title = resultSet.getString("TITLE");
		String author = resultSet.getString("AUTHOR");
		Date publicationDate = resultSet.getDate("PUB_DATE");
		double price = resultSet.getDouble("PRICE");
		Boolean available = resultSet.getBoolean("AVAILABLE");
		int quantity = resultSet.getInt("QUANTITY");
		Date receiptDate = resultSet.getDate("REC_DATE");
		Book book = new Book(bookId,title,author,publicationDate,
				price,available,quantity,receiptDate);
		boolean executedClaim = false;
		if (executed == 1){
			executedClaim = true;
		}
		
		Claim claim = new Claim(id, book, amount, executedClaim);
		
		return claim;
	}

	@Override
	public boolean existsInDB(Connection connection, Claim object) throws SQLException {
		if ( read(connection, object.getId()) != null){
			return true;
		}
		return false;
	}

	@Override
	public PreparedStatement getUpdateStatement(Connection c, Claim object)
			throws SQLException {
		PreparedStatement preparedStatement = c.prepareStatement(updateClaimQuery);
		int executed = 0;
		if (object.isExecuted()){
			executed = 1;
		}
		preparedStatement.setInt(1, object.getBook().getId());
		preparedStatement.setInt(2, object.getClaimAmount());
		preparedStatement.setInt(3, executed);
		preparedStatement.setInt(4, object.getId());
		return preparedStatement;
	}

	@Override
	public PreparedStatement getInsertStatement(Connection c, Claim object)
			throws SQLException {
		PreparedStatement preparedStatement = c.prepareStatement(insertClaimQuery);
		int executed = 0;
		if (object.isExecuted()){
			executed = 1;
		}
		preparedStatement.setInt(1, object.getBook().getId());
		preparedStatement.setInt(2, object.getClaimAmount());
		preparedStatement.setInt(3, executed);
		return preparedStatement;
	}

	@Override
	public PreparedStatement getLastInsertedStatement(Connection c)
			throws SQLException {
		return c.prepareStatement(lastInsertedClaimQuery);
		
	}

	@Override
	public PreparedStatement readStatement(Connection c, Integer PK)
			throws SQLException {
		PreparedStatement preparedStatement = c.prepareStatement(readClaimQuery);
		
		preparedStatement.setInt(1, PK);
		
		return preparedStatement;
	}

	@Override
	public PreparedStatement readAllStatement(Connection c) throws SQLException {
		PreparedStatement ps = c.prepareStatement(readAllClaimsQuery);
		return ps;
	}

	@Override
	public PreparedStatement getDeleteStatement(Connection c, Claim object)
			throws SQLException {
        PreparedStatement preparedStatement = c.prepareStatement(deleteClaimQuery);
		
		preparedStatement.setInt(1, object.getId());
		return preparedStatement;
	}

	@Override
	public PreparedStatement getDeleteByIdStatement(Connection c, Integer PK)
			throws SQLException {
PreparedStatement preparedStatement = c.prepareStatement(deleteClaimQuery);
		
		preparedStatement.setInt(1, PK);
		return preparedStatement;
	}

	@Override
	public boolean executeClaim(Connection connection, Claim object) {
		try {
         PreparedStatement preparedStatement = connection.prepareStatement(executeClaimQuery);		
			preparedStatement.setInt(1, object.getId());
			return true;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return false;
	}

	@Override
	public boolean deleteClaimByBook(Connection connection, Book book) {
		
		try {
	         PreparedStatement preparedStatement = connection.prepareStatement(deleteClaimByBook);		
				preparedStatement.setInt(1, book.getId());
				preparedStatement.executeUpdate();
				return true;
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
			}
		return false;
		
		
	}

	@Override
	public List<Claim> findClaimByBook(Connection connection, Book book) {
		List<Claim> claims  = new ArrayList<Claim>();
		try {
	         PreparedStatement preparedStatement = connection.prepareStatement(findClaimByBook);		
				preparedStatement.setInt(1, book.getId());
				ResultSet rs = preparedStatement.executeQuery();
				while(rs.next()){
					claims.add(convertResultSet(rs));
				}
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
			}
		return claims;
	}

	

	
	
}
